package cn.hicard.risk.dao.webpay.impl;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import cn.hicard.risk.dao.BaseDao;
import cn.hicard.risk.dao.webpay.TransMonitorDao;

@Repository
public class TransMonitorDaoImpl implements TransMonitorDao {

	@SuppressWarnings("unchecked")
	private BaseDao baseDao;

	@SuppressWarnings("unchecked")
	public void setBaseDao(BaseDao baseDao) {
		this.baseDao = baseDao;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> findList(String startTime, String endTime, String maxTransTime) throws Exception {
		if(endTime == null || "".equals(endTime)){
			endTime = new SimpleDateFormat("HH:mm:ss").format(new Date(System.currentTimeMillis() - 30000));
		}
		String transDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		StringBuffer sql = new StringBuffer("SELECT t.`mid` AS 'mid',t.`termid` AS termid,t.`TermType` AS termType,t.`Channel` AS channel,t.`msgcode` AS type,t.`b34_telnumber` AS orderno,t.`b37_refnumber` AS refnumber,t.`cardno`,t.`amount`,DATE_FORMAT(t.`trans_date`,'%Y-%m-%d') AS transDate, DATE_FORMAT(t.`trans_time`,'%T') AS transTime,t.`print_status` AS printStatus,t.`b39_respcode` AS respcode FROM t_trans_jnl t WHERE 1=1 and t.`msgcode` != '0620' and t.`msgcode` != '0100' and t.`amount` != '0.00' and t.`trans_date`= ? ");
		if(startTime != null && !startTime.equals("")){
			sql.append(" and t.`trans_time` >= '" + startTime + "'");
		}
		if(endTime != null && !endTime.equals("")){
			sql.append(" and t.`trans_time` <= '" + endTime + "'");
		}
		if((startTime == null || startTime.equals("") || endTime == null || endTime.equals("")) && maxTransTime != null && !maxTransTime.equals("")){
			sql.append(" and t.`trans_time` > '" + maxTransTime + "'");
		}
		sql.append(" ORDER BY t.`trans_time` DESC LIMIT 20");
		
		List<Map<String, Object>> findMapBySql = null;
		try {
			findMapBySql = baseDao.findMapBySql(sql.toString(),transDate);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return findMapBySql;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> successRateStatistics(String startTime, String endTime) throws Exception {
		if(endTime == null || "".equals("")){
			//由于交易数据有时不能及时得到银联的响应码，只查询到30秒钟前的交易记录
			endTime = new SimpleDateFormat("HH:mm:ss").format(new Date(System.currentTimeMillis() - 30000));
		}
		String transDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		String transTime = new SimpleDateFormat("HH:mm:ss").format(new Date(System.currentTimeMillis() - 10 * 60 * 1000-30000));
		StringBuffer sql1 = new StringBuffer("SELECT t1.channel AS channel,t1.sumCount AS sumCount,t2.failCount AS failCount,(t1.sumCount-t2.failCount)/t1.sumCount AS successRate FROM (SELECT channel AS 'channel',COUNT(MID) AS 'sumCount' FROM t_trans_jnl WHERE 1=1 AND msgcode != '0620' and msgcode != '0100' AND amount != '0.00' AND trans_date = ? ");
		StringBuffer sql2 = new StringBuffer("SELECT t.`Channel` AS failChannel,t.`b39_respcode` AS failRespcode,COUNT(b39_respcode) AS maxFailCount FROM t_trans_jnl t  WHERE t.b39_respcode not in ('00','51','55') AND msgcode != '0620' and msgcode != '0100' AND amount != '0.00' AND trans_date = ? AND t.`Channel` = ? ");
		StringBuffer sql3 = new StringBuffer("SELECT  t.channel AS channel,COUNT(*) AS sumCount FROM t_trans_jnl t WHERE t.b39_respcode not in ('00','51','55') AND t.msgcode != '0620' AND t.msgcode != '0100' AND t.amount != '0.00' AND t.channel IN ('1001', '1003', '1007', '1009', '2001') ");
		if(startTime != null && !startTime.equals("")){
			sql1.append(" AND trans_time >= '" + startTime + "'");
			sql2.append(" AND t.`trans_time` >= '" + startTime + "'");
			sql3.append(" AND t.trans_time >= '" + startTime + "'");
		} else {
			sql1.append(" AND trans_time >= '" + transTime + "'");
			sql2.append(" AND t.`trans_time` >= '" + transTime + "'");
			sql3.append(" AND t.trans_time >= '" + transTime + "'");
		}
		if(endTime != null && !endTime.equals("")){
			sql1.append(" AND trans_time <= '" + endTime + "'");
			sql2.append(" AND t.`trans_time` <= '" + endTime + "'");
			sql3.append(" AND t.trans_time <= '" + endTime + "'");
		}
		sql1.append(" GROUP BY channel) t1,(SELECT channel AS 'channel',COUNT(MID) AS 'failCount'  FROM t_trans_jnl WHERE b39_respcode not in ('00','51','55') AND msgcode != '0620' and msgcode != '0100' AND amount != '0.00' AND trans_date = ?");
		if(startTime != null && !startTime.equals("")){
			sql1.append(" AND trans_time >= '" + startTime + "'");
		} else {
			sql1.append(" AND trans_time >= '" + transTime + "'");
		}
		if(endTime != null && !endTime.equals("")){
			sql1.append(" AND trans_time <= '" + endTime + "'");
		}
		sql1.append(" GROUP BY channel) t2 WHERE t1.channel=t2.channel");
		sql2.append(" GROUP BY t.b39_respcode ORDER BY maxFailCount DESC LIMIT 1");
		List<Map<String, Object>> resultList1 = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> resultList2 = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> channnels = null;
		List<Map<String, Object>> failRespCodeList = null;
		List<Map<String, Object>> allSuccessList = null;
		try {
			channnels = baseDao.findMapBySql(sql1.toString(), transDate, transDate);
			if (channnels != null && channnels.size() > 0) {
				for (int i = 0; i < channnels.size(); i++) {
					failRespCodeList = baseDao.findMapBySql(sql2.toString(), transDate, channnels.get(i).get("channel"));
					if (failRespCodeList != null && failRespCodeList.size() > 0) {
						resultList1.add(i,channnels.get(i));
						resultList1.get(i).putAll(failRespCodeList.get(0));
						resultList1.get(i).put("sortNo", i+1);
					}
				}
			}
			
			//上面的resultList1只有失败的结果，如果没有失败的结果，需要做如下处理
			int index = 0;
			sql3.append(" AND t.channel NOT  IN (");
			if(resultList1 != null && resultList1.size() > 0){
				index = resultList1.size();
				for(int i = 0; i < resultList1.size(); i++){
					resultList2.add(i, resultList1.get(i));
					sql3.append("'" + resultList1.get(i).get("channel") + "',");
				}
				sql3.deleteCharAt(sql3.lastIndexOf(","));
			} else{
				sql3.append("''");
			}
			sql3.append(" ) GROUP BY  t.channel");
			allSuccessList = baseDao.findMapBySql(sql3.toString());
			if(allSuccessList != null && allSuccessList.size() > 0) {
				for(int k = 0; k < allSuccessList.size(); k++){
					resultList2.add(index + k, allSuccessList.get(k));
					resultList2.get(index + k).put("sortNo", index + k + 1);
					resultList2.get(index + k).put("failCount", "0");
					resultList2.get(index + k).put("successRate", "1.00");
					resultList2.get(index + k).put("failRespcode", "");
					resultList2.get(index + k).put("maxFailCount", "");
					resultList2.get(index + k).put("codeDesc", "");
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resultList2;
	}

	/**
	 * 异常统计
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> exceptionStatistics(String transDate, String startTime, String endTime, String msgcode) throws Exception {
		List<Map<String, Object>> failList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String failSql = "SELECT t.channel as 'channel', t.mid as 'mid',t.termid as 'termid',t.trans_date as 'transDate', t.trans_time as 'transTime', t.b39_respcode as 'respcode',COUNT(t.termid) as 'failCount' FROM t_trans_jnl t WHERE t.b39_respcode NOT IN ('00','25') AND t.trans_date=? AND t.trans_time BETWEEN ? AND ? AND t.msgcode LIKE ? GROUP BY t.mid,t.termid ORDER BY t.trans_time";
		String allSql = "SELECT t.channel as 'channel', t.mid as 'mid',t.termid as 'termid',t.trans_date as 'transDate', t.trans_time as 'transTime', t.b39_respcode as 'respcode',COUNT(t.termid) as 'allCount' FROM t_trans_jnl t WHERE   t.trans_date=? AND t.trans_time BETWEEN ? AND ? AND t.msgcode LIKE ? GROUP BY t.mid,t.termid ORDER BY t.trans_time";
		failList = baseDao.findMapBySql(failSql, transDate, startTime, endTime, msgcode);
		allList = baseDao.findMapBySql(allSql, transDate, startTime, endTime, msgcode);
		
		String channel = "";
		String allMid = "";
		String allTermid = "";
		String allCount = "0";
		String failMid = "";
		String failTermid = "";
		String failCount = "0";
		if(allList != null && allList.size() > 0 ){
			for (int i = 0; i < allList.size(); i++) {
				allMid = allList.get(i).get("mid").toString();
				allTermid = allList.get(i).get("termid").toString();
				allCount = allList.get(i).get("allCount").toString();
				if(failList != null && failList.size() > 0){
					for(int j = 0; j < failList.size(); j++) {
						failMid = failList.get(j).get("mid").toString();
						failTermid = failList.get(j).get("termid").toString();
						if(allMid.equals(failMid) && allTermid.equals(failTermid)) {
							failCount = failList.get(j).get("failCount").toString();
							break;
						}
					}
				}
				resultList.add(i,allList.get(i));
				channel = allList.get(i).get("channel") == null ? "" : allList.get(i).get("channel").toString();
				if("1001".equals(channel)){
					resultList.get(i).put("channelDesc", "广银联");
				} else if("1003".equals(channel)){
					resultList.get(i).put("channelDesc", "宁波银联");
				} else if("1007".equals(channel)){
					resultList.get(i).put("channelDesc", "杭州银联");
				} else if("1009".equals(channel)){
					resultList.get(i).put("channelDesc", "福建银联");
				} else if("2001".equals(channel)){
					resultList.get(i).put("channelDesc", "快钱");
				} else if("1017".equals(channel)){
					resultList.get(i).put("channelDesc", "易宝");
				} else {
					resultList.get(i).put("channelDesc", "");
				}
				resultList.get(i).put("failCount", failCount);
				resultList.get(i).put("sortNo", i + 1);
				DecimalFormat df = new DecimalFormat("#0.00");
				resultList.get(i).put("failRate", df.format((Double.parseDouble(failCount))/(Double.parseDouble(allCount))*100) + "%");
				channel = "";
				allMid = "";
				allTermid = "";
				allCount = "0";
				failMid = "";
				failTermid = "";
				failCount = "0";
			}
		}
		return resultList;
	}
}
